BigQueryのテーブル作成実践(データ定義言語(DDL))

#Google BigQuery

しんや

2020.03.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Google BigQueryでは、テーブル作成を行う術が複数提供されています。当エントリではその中から「データ定義言語(DDL)」による作成方法について、実践を交えて見ていきたいと思います。

データ定義言語ステートメントの使用 | BigQuery | Google Cloud

構文

DDLによるテーブル作成(CREATE TABLE)の構文は以下の通り。

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
table_name
[(
column_name column_schema[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

要点

上記ドキュメントに記載の内容について、ザッと目を通した上で気になるところをまとめてみました。

テーブル作成の際に取れる"挙動"としては何パターンか存在。

新規で作成(CREATE TABLE)
既存テーブルから新しいテーブルを作成(CREATE TABLE ... AS SELECT)
テーブルが存在していなければ作成(CREATE TABLE IF NOT EXISTS)
テーブルが存在していればそれを上書きして作成(CREATE OR REPLACE TABLE)

データ型について：詳細は下記公式ドキュメントを参照。

BigQueryで利用出来るテーブルカラムの「データ型」は以下の通り。

STRING(文字列型)
BYTES(可変長文字バイナリデータ)
INTEGER(整数型)
FLOAT(浮動小数点型)
NUMERIC(10 進 38 桁の精度と 10 進 9 桁の尺度の正確な数値)
BOOLEAN(ブール型)
TIMESTAMP(タイムスタンプ型 ※マイクロ秒の精度で、絶対的な時刻)
DATE(日付型)
TIME(時刻型 ※特定の日付とは関係無し)
DATETIME(日時型 ※年、月、日、時、分、秒、およびサブ秒)
GEOGRAPHY(地理型)
RECORD(構造体型/STRUCT)
- ネストされた列と繰り返し列の指定 | BigQuery | Google Cloud

カラムの属性としてNULLABLE, REQUIRED(NOT NULL), REPEATEDが指定出来る。

BigQuery では、列に対して次のモードがサポートされます。
モードは省略可能です。モードが指定されていない場合、
列はデフォルトの NULLABLE に設定されます。

NULLABLE: 列で NULL 値が許可されます（デフォルト）。
REQUIRED(必須): NULL 値は許可されません。
REPEATED(反復): 列に指定された型の値の配列が含まれます

モード - スキーマの指定 | BigQuery | Google Cloud

カラムのコメントはcolumn_schema内でdescriptionを使う事によって指定可能(テーブルのコメントは後述の table_option_listで指定可能)

文字列型に桁数制限は無い(!)、その代わり行の最大サイズに制限がある(100MB)のでこれを超えるとアウト。

行の最大サイズ - 100 MB
行のサイズは行データの内部表現に基づくことから、その最大サイズに対する上限は概算値です。
行の最大サイズに対する上限は、クエリジョブ実行の特定の段階で適用されます。

割り当てと上限 | BigQuery | Google Cloud

パーティションという概念がある(PARTITION BYで指定)。指定無し/取り込み時間で指定/条件を満たすデータ型のカラムで指定、が選べる

日付 / タイムスタンプパーティション分割テーブルの作成と使用 | BigQuery | Google Cloud

クラスターという概念がある(CLUSTER BYで指定)

オプションリスト(table_option_list)を使用することで、ラベルや有効期限などのテーブルオプションを設定する事が可能となっている

expiration_timestamp(TIMESTAMP)：テーブルの有効期限
partition_expiration_days(FLOAT64)：パーティションの有効期限
require_partition_filter(BOOL)：パーティションフィルタの設定有無
kms_key_name(STRING)：暗号化に用いるKMS名称
friendly_name(STRING)：テーブル名コメント？
description(STRING)：テーブル名コメント
labels(ARRAY>))：ラベル情報

CREATE TABLE実践

コンソール経由で試す

まずは一番手っ取り早いコンソール経由から試してみます。任意のデータセットから「テーブルを作成」を選択。

テーブルの作成元「空のテーブル」を指定、テーブル名も任意の内容を入力。

必要な個数分、「フィールドを追加」を押下してカラムの設定行を追加していきます。ここで指定可能な内容は「名前」「型」「モード」の3種のみ。

パーティションとクラスタの設定も以下のように個別指定可能です。「テーブルを作成」を押下。

テーブルが作成出来ました。「スキーマを編集」ボタンでカラム定義の編集が行えます。

この編集画面では、モードの一部編集、及び説明の編集が可能となっています。

編集後の画面。

また、「詳細」タブについては以下のような表示内容となっています。「説明」と「ラベル」が付与可能なのはデータセットと同様です。

テーブルの属性として変更可能なのは「有効期限」のみとなっています。

テーブルのスキーマ編集については、プルダウンを用いた上記の手順とは異なり、テキストとして編集した内容を定義として用いる事も可能です。(※この場合、NULLABLE等の指定ってどうやるんだろう？)

CLI経由(bq query)で試す

上記で作成した内容を、同じ様な定義でCLI経由(SQLとしてのCREATE TABLE文を使用)して実行してみたものが以下となります。bq queryコマンドを使ってCREATE TABLE文を実行した形です。基本的には設定に倣う形でDDLは用意出来たのですが、一部REPEATEDを指定する方法が分からずでした(この辺そもそも使い勝手とかあまり把握していないのもあるので、別途エントリを改める形で深堀りしてみたいと思います)

$ bq query --nouse_legacy_sql '
CREATE TABLE cmbqdataset.bqtable_from_cli (

  /** 列毎の定義 */
  user_id INT64 NOT NULL OPTIONS(description="ユーザーID、一意のキー"),
  user_name STRING NOT NULL OPTIONS(description="ユーザー名"),
  item_bytes BYTES,
  value_float FLOAT64,
  value_numeric NUMERIC,
  value_boolean BOOLEAN,
  birthday DATE NOT NULL OPTIONS(description="誕生日"),
  create_timestamp TIMESTAMP,
  create_datetime DATETIME,
  create_time TIME,
  item_geography GEOGRAPHY,
  item_record STRUCT <
    key STRING
  >
)
/** パーティション指定 */
PARTITION BY DATE(create_timestamp)
/** クラスタ指定 */
CLUSTER BY user_id
/** その他オプション指定 */
OPTIONS(
  expiration_timestamp=TIMESTAMP "2020-12-31 23:59:59 UTC",
  partition_expiration_days=7,
  description="a table that expires at 2021, with each partition living for 7 days",
  labels=[("production_mode", "false")]
)
'

Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ... (0s) Current status: DONE   
Created cm-xxxxxxxxxxxxx.cmbqdataset.bqtable_from_cli

クエリ実行後のコンソールで確認した内容がこちら(カラム定義)。